﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Models
{
    public class BillModel
    {
        private string connectionString;
        private SqlConnection connect;

        public BillModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public DataTable getBestSeller()
        {
            DataTable result = new DataTable();
            try
            {
                connect.Open();
                string selectSQL = "SELECT DISTINCT Top 3 F.book_id,count(F.book_id) FROM Bill_detail F INNER JOIN Book B ON F.book_id = B.book_id INNER JOIN Bill Bi ON F.bill_id=Bi.bill_id WHERE Bi.status=3 and B.status != 0 GROUP BY F.book_id Order By count(F.book_id) DESC";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(reader);
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[][] getBillRentByUserID(int user_id)
        {
            string[][] result = new string[100][];
            string[][] temp = new string[100][];
            try
            {
                connect.Open();
                
                string selectSQL_1 = "SELECT * FROM Bill WHERE user_id="+user_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();
                int i = 0;
                while (reader_1.Read())
                {
                    temp[i] = new string[11];
                    temp[i][0] = reader_1["bill_id"].ToString();
                    temp[i][1] = reader_1["payment_id"].ToString();
                    temp[i][2] = "shipping";//shipping
                    temp[i][3] = reader_1["total_bill_price"].ToString();
                    temp[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader_1["created_date"].ToString())).ToString("dd-MM-yyyy"); 
                    temp[i][5] = "Buy";
                    temp[i][6] = "total_item";// total item
                    temp[i][7] = "payment";//  payment
                    temp[i][8] = "/cart/DetailOrderBill?type=Buy&id=" + reader_1["bill_id"];
                    temp[i][9] = reader_1["shipping_detail_id"].ToString();// total payment
                    temp[i][10] = reader_1["status"].ToString();// total payment
                    i++;
                }
                reader_1.Close();

                selectSQL_1 = "SELECT * FROM Bill_Rent WHERE user_id=" + user_id;
                cmd_1 = new SqlCommand(selectSQL_1, connect);

                reader_1 = cmd_1.ExecuteReader();
                while (reader_1.Read())
                {
                    temp[i] = new string[11];
                    temp[i][0] = reader_1["bill_id"].ToString();
                    temp[i][1] = reader_1["payment_id"].ToString();
                    temp[i][2] = "shipping";//shipping
                    temp[i][3] = reader_1["total_bill_price"].ToString();
                    temp[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader_1["created_date"].ToString())).ToString("dd-MM-yyyy"); 
                    temp[i][5] = "Rent";
                    temp[i][6] = "total_item";// total item
                    temp[i][7] = "payment";//  payment
                    temp[i][8] = "/cart/DetailOrderBill?type=Rent&id=" + reader_1["bill_id"];
                    temp[i][9] = reader_1["shipping_detail_id"].ToString();// total payment
                    temp[i][10] = reader_1["status"].ToString();// total payment
                    i++;
                }
                reader_1.Close();

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            try
            {
                
                int j = 0;
                string stringstatus = "";
                foreach (string[] tem in temp)
                {
                    
                    
                    string[] shipping = new string[2];
                    if (tem != null)
                    {
                        if (tem[10] == "1")
                            stringstatus = "Mới";
                        else if (tem[10] == "2")
                            stringstatus = "Đang Xử Lý";
                        else if (tem[10] == "3")
                            stringstatus = "Hoàn Thành";
                        else if (tem[10] == "4")
                            stringstatus = "Hủy";
                        shipping = this.getShippingByShippingID(Convert.ToInt32(tem[9]));
                        string total = this.getTotalItemByBillID(tem[5], Convert.ToInt32(tem[0]));
                        string payment = this.getPaymentNameByPaymentID(Convert.ToInt32(tem[1]));

                        result[j] = new string[11];
                        result[j][0] = tem[0];
                        result[j][1] = tem[4];
                        result[j][2] = total;
                        result[j][3] = payment;
                        result[j][4] = shipping[0];
                        result[j][5] = shipping[1];
                        result[j][6] = tem[3];
                        result[j][7] = tem[5]=="Buy" ? "Bán" : "Thuê";
                        result[j][8] = tem[8];
                        result[j][9] = tem[5];
                        result[j][10] = stringstatus;
                        j++;
                    }
                }
            }
            catch (Exception err)
            {
                return null;
            }
            return result;
        }

        public string[] getShippingByShippingID(int shipping_detail_id)
        {
            string[] result = new string[2];
            string temp = "";
            try
            {
                connect.Open();
                string selectSQL_1;
                selectSQL_1 = "SELECT shipping_id FROM shipping_detail WHERE shipping_detail_id =" + shipping_detail_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();

                if (reader_1.Read())
                {
                    temp = reader_1["shipping_id"].ToString();
                }
                reader_1.Close();

                selectSQL_1 = "SELECT * FROM Shipping_method WHERE shipping_id=" + temp;
                cmd_1 = new SqlCommand(selectSQL_1, connect);

          
                reader_1 = cmd_1.ExecuteReader();
      
                if (reader_1.Read())
                {
                    result[0] = reader_1["shipping_name"].ToString();
                    result[1] = reader_1["cost"].ToString();
                }
                reader_1.Close();

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string getTotalItemByBillID(string type, int bill_id)
        {
            string result="";
            try
            {
                connect.Open();
                 string selectSQL_1;
                 if (type == "Buy")
                    selectSQL_1 = "SELECT Count(*) as total FROM bill_detail WHERE bill_id =" + bill_id;
                else
                    selectSQL_1 = "SELECT Count(*) as total FROM bill_rent_detail WHERE bill_id =" + bill_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();

                if (reader_1.Read())
                {
                    result = reader_1["total"].ToString();
                }
                reader_1.Close();
            }
            catch (Exception err)
            {
                return "0";
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        public string getPaymentNameByPaymentID(int payment_id)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL_1;
                selectSQL_1 = "SELECT payment_name FROM payment_method WHERE payment_id =" + payment_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();

                if (reader_1.Read())
                {
                    result = reader_1["payment_name"].ToString();
                }
                reader_1.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        public string getDepositByBillRentID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT deposit FROM Bill_Rent WHERE bill_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["deposit"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string getStatusByBillID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT status FROM Bill WHERE bill_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["status"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[][] getBillDetailUserID(int bill_id)
        {
            BookModel book = new BookModel();
            string[][] result = new string[100][];
            string[][] temp = new string[100][];
            string status = this.getStatusByBillID(bill_id);
            string stringstatus = "";
            if (status == "1")
                stringstatus = "Mới";
            else if (status == "2")
                stringstatus = "Đang Xử Lý";
            else if (status == "3")
                stringstatus = "Hoàn Thành";
            else if (status == "4")
                stringstatus = "Hủy";
            try
            {
                connect.Open();
                string update = "";
                string sqlquery = "select updated_date FROM Bill WHERE bill_id=" + bill_id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader = scm.ExecuteReader();

                if (reader.Read())
                {
                    update = reader["updated_date"].ToString();
                }
                reader.Close();
                string selectSQL_1 = "SELECT * FROM Bill_Detail WHERE bill_id = " + bill_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();
                int i = 0;
                while (reader_1.Read())
                {
                    temp[i] = new string[6];
                    temp[i][0] = reader_1["bill_detail_id"].ToString();
                    temp[i][1] = reader_1["price"].ToString();
                    temp[i][2] = reader_1["quantity"].ToString();
                    temp[i][3] = reader_1["total_price"].ToString();
                    temp[i][4] = reader_1["book_id"].ToString();
                    temp[i][5] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(update)).ToString("dd-MM-yyyy");
                   
                    i++;
                }
                reader_1.Close();

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            try
            {
                int j = 0;
                foreach (string[] tem in temp)
                {
                    string[] book_name = new string[2];
                    string[] book_infor;
                    if (tem != null)
                    {
                        book_infor = book.getBookbyID(Convert.ToInt32(tem[4]));
                        book_name = book.getBookNameImageByID(Convert.ToInt32(tem[4]));
                        result[j] = new string[13];
                        result[j][0] = tem[0];
                        result[j][1] = book_name[1].ToString() != "" ? book_name[1].ToString() : "/content/images/no_image.jpg";
                        result[j][2] = book_name[0];
                        result[j][3] = tem[1];
                        result[j][4] = tem[2];
                        result[j][5] = tem[3];
                        result[j][6] = tem[4];
                        result[j][7] = stringstatus;
                        result[j][8] = book_infor[2];
                        result[j][9] = book_infor[13];
                        result[j][10] =book_infor[14];
                        result[j][11] = book_infor[8];
                        result[j][12] = tem[5];
                        j++;
                    }
                    else break;
                }
            }
            catch (Exception err)
            {
                return null;
            }
            return result;
        }

        public string getStatusByBillRentID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT status FROM Bill_Rent WHERE bill_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["status"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[][] getBillRentDetailUserID(int bill_id)
        {
            BookModel book = new BookModel();
            string[][] result = new string[100][];
            string[][] temp = new string[100][];

            string status = this.getStatusByBillRentID(bill_id);
            string stringstatus = "";
            if (status == "1")
                stringstatus = "Mới";
            else if (status == "2")
                stringstatus = "Đang Xử Lý";
            else if (status == "3")
                stringstatus = "Hoàn Thành";
            else if (status == "4")
                stringstatus = "Hủy";
            try
            {
                connect.Open();
                string update = "";
                string sqlquery = "select updated_date FROM Bill_Rent WHERE bill_id=" + bill_id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader = scm.ExecuteReader();

                if (reader.Read())
                {
                    update = reader["updated_date"].ToString();
                }
                reader.Close();
                string selectSQL_1 = "SELECT * FROM Bill_Rent_Detail WHERE bill_id = " + bill_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();
                int i = 0;
                while (reader_1.Read())
                {
                    temp[i] = new string[8];
                    temp[i][0] = reader_1["detail_id"].ToString();
                    temp[i][1] = reader_1["price"].ToString();
                    temp[i][2] = reader_1["quantity"].ToString();
                    temp[i][3] = reader_1["total_price"].ToString();
                    temp[i][4] = reader_1["book_id"].ToString();
                    temp[i][5] = reader_1["days"].ToString();
                    temp[i][6] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(update)).ToString("dd-MM-yyyy");
                    temp[i][7] = new DateTime(1970, 1, 1).AddSeconds((Convert.ToInt32(update) + (Convert.ToInt32(reader_1["days"]) * 86400))).ToString("dd-MM-yyyy"); 
                    i++;
                }
                reader_1.Close();

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            try
            {
                int j = 0;
                foreach (string[] tem in temp)
                {
                    string[] book_name = new string[2];
                    string[] book_infor;
                    if (tem != null)
                    {
                        book_infor = book.getBookbyID(Convert.ToInt32(tem[4]));
                        book_name = book.getBookNameImageByID(Convert.ToInt32(tem[4]));
                        result[j] = new string[15];
                        result[j][0] = tem[0];
                        result[j][1] = book_name[1].ToString() != "" ? book_name[1].ToString() : "/content/images/no_image.jpg";
                        result[j][2] = book_name[0];
                        result[j][3] = tem[1];
                        result[j][4] = tem[5];
                        result[j][5] = tem[2];
                        result[j][6] = tem[3];
                        result[j][7] = tem[4];
                        result[j][8] = stringstatus;
                        result[j][9] = book_infor[2];
                        result[j][10] = book_infor[13];
                        result[j][11] = book_infor[14];
                        result[j][12] = book_infor[8];
                        result[j][13] = tem[6];
                        result[j][14] = tem[7];
                        
                        j++;
                    }
                }
            }
            catch (Exception err)
            {
                return null;
            }
            return result;
        }
        public string getShippingCostByBillID(string type,int bill_id)
        {
            string result = "";
            string temp = "";
            try
            {
                connect.Open();

                string selectSQL_1;
                if(type=="Buy")
                    selectSQL_1 = "SELECT shipping_detail_id FROM Bill WHERE bill_id =" + bill_id;
                else
                    selectSQL_1 = "SELECT shipping_detail_id FROM Bill_Rent WHERE bill_id =" + bill_id;
                SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                SqlDataReader reader_1;
                reader_1 = cmd_1.ExecuteReader();

                if (reader_1.Read())
                {
                    temp = reader_1["shipping_detail_id"].ToString();
                }
                reader_1.Close();
                
                selectSQL_1 = "SELECT shipping_id FROM shipping_detail WHERE shipping_detail_id =" + temp;
                cmd_1 = new SqlCommand(selectSQL_1, connect);


                reader_1 = cmd_1.ExecuteReader();

                if (reader_1.Read())
                {
                    temp = reader_1["shipping_id"].ToString();
                }
                reader_1.Close();

                selectSQL_1 = "SELECT cost FROM Shipping_method WHERE shipping_id=" + temp;
                cmd_1 = new SqlCommand(selectSQL_1, connect);


                reader_1 = cmd_1.ExecuteReader();

                if (reader_1.Read())
                {
                    result = reader_1["cost"].ToString();
                }
                reader_1.Close();

            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public void insertNewBill(string[] data)
        {
            connect.Open();
            string sqlquery = "INSERT INTO Bill (shipping_detail_id,payment_id,quantity,total_bill_price,created_date,user_id,bill_code,updated_date,status,check_quantity) VALUES (N'" + data[0] + "',N'" + data[1] + "',N'" + data[2] + "',N'" + data[3] + "',N'" + data[4] + "',N'" + data[5] + "',N'" + data[6] + "',N'" + data[7] + "',N'" + data[8] + "',0)";
            SqlCommand scd = new SqlCommand(sqlquery, connect);
            scd.ExecuteNonQuery();
            connect.Close();
        }
        public string getLastIdBill()
        {
            connect.Open();
            string id = "";
            string sqlquery = "SELECT max(bill_id) FROM Bill ";
            SqlCommand scd = new SqlCommand(sqlquery, connect);
            SqlDataReader reader = scd.ExecuteReader();
            if (reader.Read())
            {
                id = reader[0].ToString();
            }
            connect.Close();
            return id;
        }

       
    }
}
